
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/30/2013 19:07:46
-- Generated from EDMX file: C:\Users\Apolo I\Documents\Visual Studio 2010\Projects\Lola\Lola.ServidorConsola\Lola.Persistencia\ModeDatos.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [SEMINARIOI];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[fk__Incidencia_Servicio]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Incidencia] DROP CONSTRAINT [fk__Incidencia_Servicio];
GO
IF OBJECT_ID(N'[dbo].[fk_ChoferServicio_Chofer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ChoferServicio] DROP CONSTRAINT [fk_ChoferServicio_Chofer];
GO
IF OBJECT_ID(N'[dbo].[fk_ChoferServicio_ControlAlcoholemia]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ChoferServicio] DROP CONSTRAINT [fk_ChoferServicio_ControlAlcoholemia];
GO
IF OBJECT_ID(N'[dbo].[fk_ChoferServicio_Servicio]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ChoferServicio] DROP CONSTRAINT [fk_ChoferServicio_Servicio];
GO
IF OBJECT_ID(N'[dbo].[fk_Galpon_UbicacionGeografica]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Galpon] DROP CONSTRAINT [fk_Galpon_UbicacionGeografica];
GO
IF OBJECT_ID(N'[dbo].[fk_Incidencia_SectorGalpon]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Incidencia] DROP CONSTRAINT [fk_Incidencia_SectorGalpon];
GO
IF OBJECT_ID(N'[dbo].[fk_ParteNovedades_Servicio]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ParteNovedades] DROP CONSTRAINT [fk_ParteNovedades_Servicio];
GO
IF OBJECT_ID(N'[dbo].[fk_SectorGalpon_Galpon]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SectorGalpon] DROP CONSTRAINT [fk_SectorGalpon_Galpon];
GO
IF OBJECT_ID(N'[dbo].[fk_SectorGalpon_Sector]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SectorGalpon] DROP CONSTRAINT [fk_SectorGalpon_Sector];
GO
IF OBJECT_ID(N'[dbo].[fk_Servicio_Micro]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Servicio] DROP CONSTRAINT [fk_Servicio_Micro];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Chofer]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Chofer];
GO
IF OBJECT_ID(N'[dbo].[ChoferServicio]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ChoferServicio];
GO
IF OBJECT_ID(N'[dbo].[ControlAlcoholemia]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ControlAlcoholemia];
GO
IF OBJECT_ID(N'[dbo].[Galpon]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Galpon];
GO
IF OBJECT_ID(N'[dbo].[Incidencia]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Incidencia];
GO
IF OBJECT_ID(N'[dbo].[Micro]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Micro];
GO
IF OBJECT_ID(N'[dbo].[ParteNovedades]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ParteNovedades];
GO
IF OBJECT_ID(N'[dbo].[Sector]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Sector];
GO
IF OBJECT_ID(N'[dbo].[SectorGalpon]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SectorGalpon];
GO
IF OBJECT_ID(N'[dbo].[Servicio]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Servicio];
GO
IF OBJECT_ID(N'[dbo].[UbicacionGeografica]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UbicacionGeografica];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Chofer'
CREATE TABLE [dbo].[Chofer] (
    [nombre] varchar(50)  NOT NULL,
    [apellido] varchar(50)  NOT NULL,
    [dni] int  NOT NULL,
    [fecVecReg] datetime  NOT NULL,
    [empresa] varchar(100)  NOT NULL,
    [legajo] int  NOT NULL,
    [fechaNac] datetime  NOT NULL,
    [nacionalidad] varchar(30)  NOT NULL
);
GO

-- Creating table 'ChoferServicio'
CREATE TABLE [dbo].[ChoferServicio] (
    [legajo] int  NOT NULL,
    [cod] varchar(10)  NOT NULL,
    [idTestAlc] int  NOT NULL
);
GO

-- Creating table 'ControlAlcoholemia'
CREATE TABLE [dbo].[ControlAlcoholemia] (
    [resAlc] int  NOT NULL,
    [fechaConAlc] datetime  NOT NULL,
    [idTestAlc] int  NOT NULL,
    [apto] bit  NULL
);
GO

-- Creating table 'Galpon'
CREATE TABLE [dbo].[Galpon] (
    [ubicacion] int  NOT NULL,
    [nomGal] varchar(100)  NOT NULL,
    [idGal] int  NOT NULL
);
GO

-- Creating table 'Incidencia'
CREATE TABLE [dbo].[Incidencia] (
    [numeroInc] int  NOT NULL,
    [fechInc] datetime  NOT NULL,
    [cod] varchar(10)  NOT NULL,
    [idConv] int  NOT NULL,
    [gravedad] varchar(10)  NOT NULL,
    [descrip] varchar(300)  NOT NULL,
    [prioridad] int  NOT NULL,
    [estado] varchar(3)  NULL
);
GO

-- Creating table 'Micro'
CREATE TABLE [dbo].[Micro] (
    [paten] varchar(6)  NOT NULL,
    [mode] int  NOT NULL,
    [numInt] int  NOT NULL,
    [cantPasa] int  NOT NULL,
    [km] int  NULL,
    [calidad] varchar(15)  NULL
);
GO

-- Creating table 'ParteNovedades'
CREATE TABLE [dbo].[ParteNovedades] (
    [numNov] int  NOT NULL,
    [fechaAlta] datetime  NOT NULL,
    [obsNov] varchar(150)  NULL,
    [cod] varchar(10)  NOT NULL
);
GO

-- Creating table 'Sector'
CREATE TABLE [dbo].[Sector] (
    [nomSec] varchar(200)  NULL,
    [numSec] int  NOT NULL,
    [actSec] varchar(15)  NOT NULL
);
GO

-- Creating table 'SectorGalpon'
CREATE TABLE [dbo].[SectorGalpon] (
    [numSec] int  NOT NULL,
    [idGal] int  NOT NULL,
    [orden] int  NOT NULL,
    [idConv] int  NOT NULL
);
GO

-- Creating table 'Servicio'
CREATE TABLE [dbo].[Servicio] (
    [cod] varchar(10)  NOT NULL,
    [fechaSalidaSer] datetime  NOT NULL,
    [fechaLlegadaSer] datetime  NOT NULL,
    [origen] varchar(50)  NOT NULL,
    [destino] varchar(50)  NOT NULL,
    [numInt] int  NOT NULL,
    [estado] varchar(15)  NOT NULL
);
GO

-- Creating table 'UbicacionGeografica'
CREATE TABLE [dbo].[UbicacionGeografica] (
    [localidad] varchar(150)  NULL,
    [provincia] varchar(200)  NULL,
    [pais] varchar(100)  NULL,
    [idUbi] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [legajo] in table 'Chofer'
ALTER TABLE [dbo].[Chofer]
ADD CONSTRAINT [PK_Chofer]
    PRIMARY KEY CLUSTERED ([legajo] ASC);
GO

-- Creating primary key on [legajo], [cod] in table 'ChoferServicio'
ALTER TABLE [dbo].[ChoferServicio]
ADD CONSTRAINT [PK_ChoferServicio]
    PRIMARY KEY CLUSTERED ([legajo], [cod] ASC);
GO

-- Creating primary key on [idTestAlc] in table 'ControlAlcoholemia'
ALTER TABLE [dbo].[ControlAlcoholemia]
ADD CONSTRAINT [PK_ControlAlcoholemia]
    PRIMARY KEY CLUSTERED ([idTestAlc] ASC);
GO

-- Creating primary key on [idGal] in table 'Galpon'
ALTER TABLE [dbo].[Galpon]
ADD CONSTRAINT [PK_Galpon]
    PRIMARY KEY CLUSTERED ([idGal] ASC);
GO

-- Creating primary key on [numeroInc] in table 'Incidencia'
ALTER TABLE [dbo].[Incidencia]
ADD CONSTRAINT [PK_Incidencia]
    PRIMARY KEY CLUSTERED ([numeroInc] ASC);
GO

-- Creating primary key on [numInt] in table 'Micro'
ALTER TABLE [dbo].[Micro]
ADD CONSTRAINT [PK_Micro]
    PRIMARY KEY CLUSTERED ([numInt] ASC);
GO

-- Creating primary key on [numNov] in table 'ParteNovedades'
ALTER TABLE [dbo].[ParteNovedades]
ADD CONSTRAINT [PK_ParteNovedades]
    PRIMARY KEY CLUSTERED ([numNov] ASC);
GO

-- Creating primary key on [numSec] in table 'Sector'
ALTER TABLE [dbo].[Sector]
ADD CONSTRAINT [PK_Sector]
    PRIMARY KEY CLUSTERED ([numSec] ASC);
GO

-- Creating primary key on [idConv] in table 'SectorGalpon'
ALTER TABLE [dbo].[SectorGalpon]
ADD CONSTRAINT [PK_SectorGalpon]
    PRIMARY KEY CLUSTERED ([idConv] ASC);
GO

-- Creating primary key on [cod] in table 'Servicio'
ALTER TABLE [dbo].[Servicio]
ADD CONSTRAINT [PK_Servicio]
    PRIMARY KEY CLUSTERED ([cod] ASC);
GO

-- Creating primary key on [idUbi] in table 'UbicacionGeografica'
ALTER TABLE [dbo].[UbicacionGeografica]
ADD CONSTRAINT [PK_UbicacionGeografica]
    PRIMARY KEY CLUSTERED ([idUbi] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [legajo] in table 'ChoferServicio'
ALTER TABLE [dbo].[ChoferServicio]
ADD CONSTRAINT [fk_ChoferServicio_Chofer]
    FOREIGN KEY ([legajo])
    REFERENCES [dbo].[Chofer]
        ([legajo])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [idTestAlc] in table 'ChoferServicio'
ALTER TABLE [dbo].[ChoferServicio]
ADD CONSTRAINT [fk_ChoferServicio_ControlAlcoholemia]
    FOREIGN KEY ([idTestAlc])
    REFERENCES [dbo].[ControlAlcoholemia]
        ([idTestAlc])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk_ChoferServicio_ControlAlcoholemia'
CREATE INDEX [IX_fk_ChoferServicio_ControlAlcoholemia]
ON [dbo].[ChoferServicio]
    ([idTestAlc]);
GO

-- Creating foreign key on [cod] in table 'ChoferServicio'
ALTER TABLE [dbo].[ChoferServicio]
ADD CONSTRAINT [fk_ChoferServicio_Servicio]
    FOREIGN KEY ([cod])
    REFERENCES [dbo].[Servicio]
        ([cod])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk_ChoferServicio_Servicio'
CREATE INDEX [IX_fk_ChoferServicio_Servicio]
ON [dbo].[ChoferServicio]
    ([cod]);
GO

-- Creating foreign key on [ubicacion] in table 'Galpon'
ALTER TABLE [dbo].[Galpon]
ADD CONSTRAINT [fk_Galpon_UbicacionGeografica]
    FOREIGN KEY ([ubicacion])
    REFERENCES [dbo].[UbicacionGeografica]
        ([idUbi])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk_Galpon_UbicacionGeografica'
CREATE INDEX [IX_fk_Galpon_UbicacionGeografica]
ON [dbo].[Galpon]
    ([ubicacion]);
GO

-- Creating foreign key on [idGal] in table 'SectorGalpon'
ALTER TABLE [dbo].[SectorGalpon]
ADD CONSTRAINT [fk_SectorGalpon_Galpon]
    FOREIGN KEY ([idGal])
    REFERENCES [dbo].[Galpon]
        ([idGal])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk_SectorGalpon_Galpon'
CREATE INDEX [IX_fk_SectorGalpon_Galpon]
ON [dbo].[SectorGalpon]
    ([idGal]);
GO

-- Creating foreign key on [cod] in table 'Incidencia'
ALTER TABLE [dbo].[Incidencia]
ADD CONSTRAINT [fk__Incidencia_Servicio]
    FOREIGN KEY ([cod])
    REFERENCES [dbo].[Servicio]
        ([cod])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk__Incidencia_Servicio'
CREATE INDEX [IX_fk__Incidencia_Servicio]
ON [dbo].[Incidencia]
    ([cod]);
GO

-- Creating foreign key on [idConv] in table 'Incidencia'
ALTER TABLE [dbo].[Incidencia]
ADD CONSTRAINT [fk_Incidencia_SectorGalpon]
    FOREIGN KEY ([idConv])
    REFERENCES [dbo].[SectorGalpon]
        ([idConv])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk_Incidencia_SectorGalpon'
CREATE INDEX [IX_fk_Incidencia_SectorGalpon]
ON [dbo].[Incidencia]
    ([idConv]);
GO

-- Creating foreign key on [numInt] in table 'Servicio'
ALTER TABLE [dbo].[Servicio]
ADD CONSTRAINT [fk_Servicio_Micro]
    FOREIGN KEY ([numInt])
    REFERENCES [dbo].[Micro]
        ([numInt])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk_Servicio_Micro'
CREATE INDEX [IX_fk_Servicio_Micro]
ON [dbo].[Servicio]
    ([numInt]);
GO

-- Creating foreign key on [cod] in table 'ParteNovedades'
ALTER TABLE [dbo].[ParteNovedades]
ADD CONSTRAINT [fk_ParteNovedades_Servicio]
    FOREIGN KEY ([cod])
    REFERENCES [dbo].[Servicio]
        ([cod])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk_ParteNovedades_Servicio'
CREATE INDEX [IX_fk_ParteNovedades_Servicio]
ON [dbo].[ParteNovedades]
    ([cod]);
GO

-- Creating foreign key on [numSec] in table 'SectorGalpon'
ALTER TABLE [dbo].[SectorGalpon]
ADD CONSTRAINT [fk_SectorGalpon_Sector]
    FOREIGN KEY ([numSec])
    REFERENCES [dbo].[Sector]
        ([numSec])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'fk_SectorGalpon_Sector'
CREATE INDEX [IX_fk_SectorGalpon_Sector]
ON [dbo].[SectorGalpon]
    ([numSec]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------